package jdbcTest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import jdbcutils.JDBCUtils;

import pojo.student;

public class StuManage{

    public static void main(String[] args) throws Exception {
        boolean tag=true;
        Scanner sc=new Scanner(System.in);

        while(tag) {
            System.out.println("欢迎来到学生管理系统\n"+"1.插入 2.删除 3.查询 4.修改 5.退出学生信息管理系统");
            int a=sc.nextInt();
            switch (a) {
                case 1:  System.out.println("请输入新生的学号");
                    String sno=sc.next();
                    System.out.println("请输入新生的姓名");
                    String sname=sc.next();
                    System.out.println("请输入新生的性别");
                    String sex=sc.next();
                    System.out.println("请输入新生的年龄");
                    int sage=sc.nextInt();
                    System.out.println("请输入新生的部门");
                    String sdept=sc.next();
                    StuManage.insert(sno,sname,sex,sage,sdept);
                    break;
                case 2:
                    System.out.println("输入要删除学生的学号");
                    String sno2=sc.next();
                    StuManage.Delete(sno2);
                    break;
                case 3:
                    List<student> list=	StuManage.findall();
                    System.out.println(list);
                    break;
                case 4:
                    System.out.println("请输入学号来修改部门和年龄");
                    String sno3=sc.next();
                    System.out.println("输入要修改成的部门");
                    String sdept2=sc.next();
                    System.out.println("输入要修改成的年龄");
                    int age=sc.nextInt();
                    change(sno3,sdept2,age);  //根据学号修改部门和年龄
                    break;
                case 5:
                    Default :tag=false;
                    break;
            }


        }

    }

    public  static int insert(String sno,String sname,String sex,int sage ,String sdept) throws Exception {
        if(sno==null) {
            return 0;
        }
        Connection conn = null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;


        try {
            conn= JDBCUtils.getConnection();
            String sql="insert into student values(?,?,?,?,?)";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, sno);
            pstmt.setString(2,sname );
            pstmt.setString(3, sex);
            pstmt.setInt(4, sage);
            pstmt.setString(5,sdept );
            int i=  pstmt.executeUpdate();

            if(i>0) {
                System.out.println("插入成功");
            }
            else {

                System.out.println("插入失败");
            }
            return i;

        }catch(Exception e) {
            e.printStackTrace();
        }
        JDBCUtils.close(conn, pstmt,null);
        return 0;

    }
    public  static int Delete(String sno) {
        if(sno==null){
            return 0;
        }
        Connection conn = null;
        PreparedStatement pstmt=null;


        try {
            conn=JDBCUtils.getConnection();
            String sql2="delete  from student where sno=?";
            pstmt=conn.prepareStatement(sql2);
            pstmt.setString(1, sno);
            int i=  pstmt.executeUpdate();
            if(i>0) {
                System.out.println("删除成功");
            }
            else {

                System.out.println("删除失败");
            }
            return i;

        }catch(Exception e) {
            e.printStackTrace();
        }
        JDBCUtils.close(conn, pstmt,null);

        return 0;
    }
    public static int change(String sno, String sdept,int sage) {
        if(sno==null) {
            return  0;
        }
        Connection conn=null;
        PreparedStatement  pstmt=null;
        ResultSet rs=null;
        try {
            conn=JDBCUtils.getConnection();
            String SQL ="update  student set sage=? ,sdept=? where sno=?";//根据学号修改部门和年龄
            pstmt=conn.prepareStatement(SQL);//给pstmt传入SQL对象
            pstmt.setInt(1, sage);
            pstmt.setString(2, sdept);
            pstmt.setString(3, sno);       //(a'or'a'='a)防止SQL注入
            int i=pstmt.executeUpdate();   //不用传SQL参数
            if(i>0) {
                System.out.println("修改成功");
            }
            else {

                System.out.println("修改失败");
            }
            return i;
        }catch (Exception e){
            e.printStackTrace();
        }

        finally {
            JDBCUtils.close(conn, pstmt, rs);
        }

        return 0;
    }






    // 查讯所有student对象
    public static List<student> findall() throws Exception {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<student> list = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String sql = "select * from student";
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "123456");
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            student st = null;
            list = new ArrayList<student>();
            while (rs.next()) {
                int sno = rs.getInt(1);
                String sname = rs.getString(2);
                String sex = rs.getString(3);
                int sage = rs.getInt(4);
                String sdept = rs.getString(5);
                st = new student();
                st.setSno(sno);
                st.setSname(sname);
                st.setSex(sex);
                st.setSage(sage);
                st.setSdept(sdept);
                list.add(st);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        JDBCUtils.close(conn, stmt,rs);
        return list;
    }

}

